/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.UserDTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Arturia
 */
public class UserDAO {

    public static ArrayList LoadDatabase(Connection con, UserDTO user) {
        ArrayList<UserDTO> result = new ArrayList<UserDTO>();
        String sql = "";
        PreparedStatement stm = null;
        try {
            if (user == null) {
                sql = "SELECT * FROM t_user";
                stm = con.prepareStatement(sql);
            }

            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                UserDTO userTemp = new UserDTO();
                userTemp = new UserDTO();

                userTemp.setStrUserID(rs.getString(1));

                if (rs.wasNull()) {
                    userTemp.setStrUserID("");
                }

                userTemp.setStrUsername(rs.getString(2));

                if (rs.wasNull()) {
                    userTemp.setStrUsername("");
                }

                userTemp.setStrPassword(rs.getString(3));

                if (rs.wasNull()) {
                    userTemp.setStrPassword("");
                }

                userTemp.setStrStatus(rs.getString(4));

                if(rs.wasNull()){
                    userTemp.setStrStatus("");
                }

                userTemp.setStrPersonID(rs.getString(5));

                if (rs.wasNull()) {
                    userTemp.setStrPersonID("");
                }

                //do table khong co field role nen set la ""
                userTemp.setStrRole("");
                userTemp.setStrRoleID("");

                result.add(userTemp);
            }
            stm.close();
            //con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
   }

    public static UserDTO checkUserRole(Connection con, UserDTO user){
        String sql = "";
        PreparedStatement stm = null;
        ResultSet rs = null;
        try{
            sql = "SELECT * FROM t_student WHERE person_id = ?";
            stm = con.prepareStatement(sql);
            stm.setString(1, user.getStrPersonID());
            rs = stm.executeQuery();
            if(rs.next()){
                user.setStrRole("Student");
                user.setStrRoleID(rs.getString(1));
                con.close();
                return user;
            }else{
                sql = "SELECT * FROM t_teacher WHERE person_id = ?";
                stm = con.prepareStatement(sql);
                stm.setString(1, user.getStrPersonID());
                rs = stm.executeQuery();
                if(rs.next()){
                    user.setStrRole("Teacher");
                    user.setStrRoleID(rs.getString(1));
                    con.close();
                    return user;
                }
                con.close();
                return user;
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return user;
    }
}
